package com.bank.dao;

import com.bank.entity.Card;
import com.bank.entity.Transaction;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;
import java.util.logging.Logger;

public class CardDAO {
    private static final boolean USE_VIEW = true; // 是否使用视图查询
    private static final Logger LOGGER = Logger.getLogger(CardDAO.class.getName());

    // 新增TransactionDAO实例
    private final TransactionDAO transactionDAO = new TransactionDAO();

    // 根据客户ID查询银行卡
    public List<Card> getCardsByCustomerId(int customerId) throws SQLException {
        List<Card> cards = new ArrayList<>();
        String sql = USE_VIEW ?
                "SELECT * FROM v_中文银行卡信息 WHERE 客户编号 = ?" :
                "SELECT * FROM cardInfo WHERE customerID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, customerId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    cards.add(mapResultSetToCard(rs));
                }
            }
        }
        return cards;
    }

    // 根据卡号查询余额
    public double getCardBalance(String cardID) throws SQLException {
        String sql = USE_VIEW ?
                "SELECT 账户余额 FROM v_中文银行卡信息 WHERE 卡号 = ?" :
                "SELECT balance FROM cardInfo WHERE cardID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? rs.getDouble(1) : 0.0;
            }
        }
    }

    // 添加银行卡
    public void addCard(Card card) throws SQLException {
        String sql = USE_VIEW ?
                "INSERT INTO cardInfo (cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)" :
                "INSERT INTO cardInfo (cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, card.getCardID());
            ps.setString(2, card.getCurID());
            ps.setInt(3, card.getSavingID());
            ps.setDate(4, new java.sql.Date(card.getOpenDate().getTime()));
            ps.setDouble(5, Math.max(card.getOpenMoney(), 1.00));
            ps.setDouble(6, Math.max(card.getBalance(), 1.00));
            ps.setString(7, card.getPassword());
            ps.setString(8, card.getIsReportLoss());
            ps.setInt(9, card.getCustomerID());
            ps.executeUpdate();
        }
    }

    // 查询所有银行卡
    public List<Card> getAllCards() throws SQLException {
        List<Card> list = new ArrayList<>();
        String sql = USE_VIEW ?
                "SELECT * FROM v_中文银行卡信息" :
                "SELECT * FROM cardInfo";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                list.add(mapResultSetToCard(rs));
            }
        }
        return list;
    }

    // 精确查询：根据卡号查询银行卡
    public Card getCardByID(String cardID) throws SQLException {
        String sql = USE_VIEW ?
                "SELECT * FROM v_中文银行卡信息 WHERE 卡号 = ?" :
                "SELECT * FROM cardInfo WHERE cardID=?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? mapResultSetToCard(rs) : null;
            }
        }
    }

    // 模糊查询：根据卡号前缀查询
    public List<Card> getCardsByCardIDPrefix(String prefix) throws SQLException {
        List<Card> list = new ArrayList<>();
        String sql = USE_VIEW ?
                "SELECT * FROM v_中文银行卡信息 WHERE 卡号 LIKE ?" :
                "SELECT * FROM cardInfo WHERE cardID LIKE ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, prefix + "%");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapResultSetToCard(rs));
                }
            }
        }
        return list;
    }

    // 范围查询：根据开户日期范围查询
    public List<Card> getCardsByOpenDateRange(Date startDate, Date endDate) throws SQLException {
        List<Card> list = new ArrayList<>();
        String sql = USE_VIEW ?
                "SELECT * FROM v_中文银行卡信息 WHERE 开户日期 BETWEEN ? AND ?" :
                "SELECT * FROM cardInfo WHERE openDate BETWEEN ? AND ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDate(1, new java.sql.Date(startDate.getTime()));
            ps.setDate(2, new java.sql.Date(endDate.getTime()));
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapResultSetToCard(rs));
                }
            }
        }
        return list;
    }

    // 取消挂失
    public boolean reportCancel(String cardID, String password) throws SQLException {
        // 首先验证密码
        if (!validatePassword(cardID, password)) {
            return false;
        }

        String sql = USE_VIEW ?
                "UPDATE cardInfo SET IsReportLoss = '否' WHERE cardID = ?" :
                "UPDATE cardInfo SET IsReportLoss = '否' WHERE cardID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            return ps.executeUpdate() > 0;
        }
    }

    // 修改银行卡密码
    public boolean changePassword(String cardID, String oldPassword, String newPassword) throws SQLException {
        // 首先验证旧密码
        if (!validatePassword(cardID, oldPassword)) {
            return false;
        }

        String sql = USE_VIEW ?
                "UPDATE cardInfo SET pass = ? WHERE cardID = ?" :
                "UPDATE cardInfo SET pass = ? WHERE cardID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, newPassword);
            ps.setString(2, cardID);
            return ps.executeUpdate() > 0;//关键执行语句 - 触发数据库触发器
        }
    }

    // 更新银行卡信息
    public void updateCard(Card card) throws SQLException {
        String sql = USE_VIEW ?
                "UPDATE cardInfo SET curID=?, savingID=?, openDate=?, openMoney=?, balance=?, pass=?, IsReportLoss=?, customerID=? WHERE cardID=?" :
                "UPDATE cardInfo SET curID=?, savingID=?, openDate=?, openMoney=?, balance=?, pass=?, IsReportLoss=?, customerID=? WHERE cardID=?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, card.getCurID());
            ps.setInt(2, card.getSavingID());
            ps.setDate(3, new java.sql.Date(card.getOpenDate().getTime()));
            ps.setDouble(4, Math.max(card.getOpenMoney(), 1.00));
            ps.setDouble(5, Math.max(card.getBalance(), 1.00));
            ps.setString(6, card.getPassword());
            ps.setString(7, card.getIsReportLoss());
            ps.setInt(8, card.getCustomerID());
            ps.setString(9, card.getCardID());
            ps.executeUpdate();
        }
    }

    // 删除银行卡
    public boolean deleteCard(String cardID) throws SQLException {
        String sql = USE_VIEW ?
                "DELETE FROM cardInfo WHERE cardID=?" :
                "DELETE FROM cardInfo WHERE cardID=?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            return ps.executeUpdate() > 0;//关键执行语句 - 触发数据库触发器
        }
    }

    // 存款
    public boolean deposit(String cardNumber, String password, double amount) throws SQLException {
        // 验证密码
        if (!validatePassword(cardNumber, password)) {
            return false;
        }

        // 检查卡片挂失状态
        if (isCardReportedLoss(cardNumber)) {
            return false;
        }

        String sql = USE_VIEW ?
                "UPDATE cardInfo SET balance = balance + ? WHERE cardID = ?" :
                "UPDATE cardInfo SET balance = balance + ? WHERE cardID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDouble(1, amount);
            ps.setString(2, cardNumber);
           //关键执行语句 - 触发数据库触发器
            return ps.executeUpdate() > 0;
        }
    }

    // 取款
    public boolean withdraw(String cardNumber, String password, double amount) throws SQLException {
        // 验证密码
        if (!validatePassword(cardNumber, password)) {
            return false;
        }

        // 检查卡片挂失状态
        if (isCardReportedLoss(cardNumber)) {
            return false;
        }

        // 检查余额是否足够
        if (getCardBalance(cardNumber) < amount) {
            return false;
        }

        String sql = USE_VIEW ?
                "UPDATE cardInfo SET balance = balance - ? WHERE cardID = ?" :
                "UPDATE cardInfo SET balance = balance - ? WHERE cardID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDouble(1, amount);
            ps.setString(2, cardNumber);
            return ps.executeUpdate() > 0;//关键执行语句 - 触发数据库触发器
        }
    }

    // transfer方法
    public boolean transfer(String fromCardID, String toCardID, String password, double amount) throws SQLException {
        // 验证密码
        if (!validatePassword(fromCardID, password)) {
            LOGGER.warning("密码验证失败: " + fromCardID);
            return false;
        }

        try (Connection conn = DBUtil.getConnection();
             CallableStatement cstmt = conn.prepareCall("{call proc_transfer1(?, ?, ?, ?, ?)}")) {

            // 注册输出参数
            cstmt.registerOutParameter(4, Types.INTEGER);
            cstmt.registerOutParameter(5, Types.VARCHAR);

            // 设置输入参数
            cstmt.setString(1, fromCardID);
            cstmt.setString(2, toCardID);
            cstmt.setDouble(3, amount);

            // 执行存储过程
            cstmt.execute();

            // 获取结果
            int resultCode = cstmt.getInt(4);
            String resultMsg = cstmt.getString(5);

            if (resultCode != 0) {
                LOGGER.warning("转账失败: " + resultMsg);
                return false;
            }
            return true;

        } catch (SQLException e) {
            LOGGER.severe("转账过程中发生错误: " + e.getMessage());
            throw e;
        }
    }

    // 挂失
    public boolean reportLoss(String cardID, String password) throws SQLException {
        // 首先验证密码
        if (!validatePassword(cardID, password)) {
            return false;
        }

        String sql = USE_VIEW ?
                "UPDATE cardInfo SET IsReportLoss = '是' WHERE cardID = ?" :
                "UPDATE cardInfo SET IsReportLoss = '是' WHERE cardID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            return ps.executeUpdate() > 0;
        }
    }

    // 注册银行卡
    public void registerCard(String password, String pid, String depositType, String curID, String openDateStr, double openMoney) throws SQLException {
        String sql = "INSERT INTO cardInfo (cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement psGetCustomerID = conn.prepareStatement(
                     "SELECT customerID FROM userInfo WHERE PID = ?")) {
            psGetCustomerID.setString(1, pid);
            try (ResultSet rs = psGetCustomerID.executeQuery()) {
                if (rs.next()) {
                    int customerID = rs.getInt("customerID");
                    String cardID = generateCardID();
                    java.sql.Date openDate = java.sql.Date.valueOf(openDateStr);

                    try (PreparedStatement ps = conn.prepareStatement(sql)) {
                        ps.setString(1, cardID);
                        ps.setString(2, curID);
                        ps.setInt(3, Integer.parseInt(depositType));
                        ps.setDate(4, openDate);
                        ps.setDouble(5, Math.max(openMoney, 1.00));
                        ps.setDouble(6, Math.max(openMoney, 1.00));
                        ps.setString(7, password);
                        ps.setString(8, "否");
                        ps.setInt(9, customerID);
                        ps.executeUpdate();
                    }
                }
            }
        }
    }

    // 检查卡号是否存在
    public boolean cardExists(String cardID) throws SQLException {
        String sql;
        if (USE_VIEW) {
            // 修正：添加空格并处理中文视图名
            sql = "SELECT COUNT(*) FROM `v_中文银行卡信息` WHERE `卡号` = ?";
        } else {
            sql = "SELECT COUNT(*) FROM cardInfo WHERE cardID = ?";
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

    // 生成随机卡号
    private String generateCardID() {
        Random random = new Random();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < 16; i++) {
            sb.append(random.nextInt(10));
        }
        return sb.toString();
    }

    // 检查卡片挂失状态
    public boolean isCardReportedLoss(String cardID) throws SQLException {
        String sql = USE_VIEW ?
                "SELECT 是否挂失 FROM v_中文银行卡信息 WHERE 卡号 = ?" :
                "SELECT IsReportLoss FROM cardInfo WHERE cardID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() && "是".equals(rs.getString(1));
            }
        }
    }

    public boolean createNewCardForUser(int customerID, int savingID, String password, double openMoney) throws SQLException {
        String cardID = generateCardID();
        String sql = USE_VIEW ?
                "INSERT INTO cardInfo (cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID) " +
                        "VALUES (?, 'RMB', ?, CURDATE(), ?, ?, ?, '否', ?)" :
                "INSERT INTO cardInfo (cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID) " +
                        "VALUES (?, 'RMB', ?, CURDATE(), ?, ?, ?, '否', ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            ps.setInt(2, savingID);
            ps.setDouble(3, openMoney);
            ps.setDouble(4, openMoney);
            ps.setString(5, password);
            ps.setInt(6, customerID);
            ps.executeUpdate();
            return true;
        }
    }

    // 统一结果集封装
    private Card mapResultSetToCard(ResultSet rs) throws SQLException {
        Card card = new Card();

        if (USE_VIEW) {
            // 视图列名（中文）
            card.setCardID(rs.getString("卡号"));
            card.setCurID(rs.getString("币种"));
            card.setSavingID(rs.getInt("存款类型"));
            card.setOpenDate(rs.getDate("开户日期"));
            card.setOpenMoney(rs.getDouble("开卡金额"));
            card.setBalance(rs.getDouble("账户余额"));
            card.setPassword(rs.getString("密码"));
            card.setIsReportLoss(rs.getString("是否挂失"));
            card.setCustomerID(rs.getInt("客户编号"));
        } else {
            // 原始表列名（英文）
            card.setCardID(rs.getString("cardID"));
            card.setCurID(rs.getString("curID"));
            card.setSavingID(rs.getInt("savingID"));
            card.setOpenDate(rs.getDate("openDate"));
            card.setOpenMoney(rs.getDouble("openMoney"));
            card.setBalance(rs.getDouble("balance"));
            card.setPassword(rs.getString("pass"));
            card.setIsReportLoss(rs.getString("IsReportLoss"));
            card.setCustomerID(rs.getInt("customerID"));
        }

        return card;
    }

    // 验证银行卡密码（辅助方法）
    private boolean validatePassword(String cardID, String password) throws SQLException {
        String sql = USE_VIEW ?
                "SELECT COUNT(*) FROM v_中文银行卡信息 WHERE 卡号 = ? AND 密码 = ?" :
                "SELECT COUNT(*) FROM cardInfo WHERE cardID = ? AND pass = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            ps.setString(2, password);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }
}